package com.jspgou.cms.dao.impl;

import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.Iterator;
import java.util.List;

import org.hibernate.Query;
import org.springframework.stereotype.Repository;

import com.jspgou.cms.dao.OrderItemDao;
import com.jspgou.cms.entity.OrderItem;
import com.jspgou.common.hibernate3.Finder;
import com.jspgou.common.hibernate3.HibernateBaseDao;
import com.jspgou.common.page.Pagination;

@Repository
public class OrderItemDaoImpl extends HibernateBaseDao<OrderItem, Long> implements OrderItemDao{

	protected Class<OrderItem> getEntityClass() {
		return OrderItem.class;
	}
	
	public OrderItem findById(Long id){
		OrderItem entity = get(id);
		return entity;
	}

	public List<Object[]> profitTop(Long ctgid,Long typeid,Integer pageNo,Integer pageSize) {
		Query query = getSession().createQuery(getHQL(ctgid, typeid));
		if(ctgid!=null){
			query.setLong("ctgid", ctgid);
		}
		if(typeid!=null){
			query.setLong("typeid", typeid);
		}
		Iterator iter = query.setFirstResult((pageNo-1)*pageSize).setMaxResults(pageSize).iterate();
		List<Object[]> list = new ArrayList<Object[]>();
		while(iter.hasNext()){
			list.add((Object[]) iter.next());
		}
		return list;
	}
	
	public Integer totalCount(Long ctgid,Long typeid) {
		Integer allPage = 0;
		Query query = getSession().createQuery(getCountHQL(ctgid, typeid));
		if(ctgid!=null){
			query.setLong("ctgid", ctgid);
		}
		if(typeid!=null){
			query.setLong("typeid", typeid);
		}
		Iterator iterator = query.iterate();
		if(iterator.hasNext()){
			allPage = Integer.parseInt(iterator.next()+"");
		}
		return allPage;
	}
	
	public Object[] getPageByMember(Long webId,int status,Long memberId,int pageNo,int pageSize){//会员中心购买记录
		Object[] o = new Object[2];
		Query q = getSession().createQuery("from OrderItem bean where bean.itemOrder.member.id=:id and bean.itemOrder.status=40");
		q.setLong("id", memberId);
		Integer totalCount=q.list().size();
		Iterator<OrderItem> it = q.setFirstResult((pageNo-1)*pageSize).setMaxResults(pageSize).iterate();
		List<OrderItem> list = new ArrayList<OrderItem>();
		while(it.hasNext()){
			list.add(it.next());
		}
		o[0] = list;
		o[1] = totalCount;
		return o;
	}
	
	public Pagination getPage(Long webId,Long productId,Long paymentId,Long shippingId,Long storeId,
			String code,Date startTime,Date endTime,Integer status,Integer paymentStatus,
			Integer shippingStatus,int pageNo,int pageSize){
		Finder f=Finder.create("select bean from OrderItem bean ");
		f.append(" where 1=1");
		if(webId!=null){
			f.append(" and bean.website.id=:webId");
			f.setParam("webId", webId);
		}
		if(productId!=null){
			f.append(" and bean.product.id=:productId");
			f.setParam("productId", productId);
		}
		if(paymentId!=null){
			f.append(" and bean.itemOrder.payment.id=:paymentId");
			f.setParam("paymentId", paymentId);
		}
		if(shippingId!=null){
			f.append(" and bean.itemOrder.shipping.id=:shippingId");
			f.setParam("shippingId", shippingId);
		}
		if(storeId!=null){
			f.append(" and bean.product.store.id=:storeId");
			f.setParam("storeId", storeId);
		}
		if(startTime!=null){
			f.append(" and bean.itemOrder.createTime>:startTime");
			f.setParam("startTime", startTime);
		}
		if(endTime!=null){
			f.append(" and bean.itemOrder.createTime<:endTime");
			f.setParam("endTime", endTime);
		}
		if(code!=null){
			f.append(" and bean.itemOrder.code like:code");
			f.setParam("code", "%"+code+"%");
		}
		if(status!=null){
			f.append(" and bean.itemOrder.status=:status");
			f.setParam("status", status);
		}
		if(paymentStatus!=null){
			f.append(" and bean.itemOrder.paymentStatus=:paymentStatus");
			f.setParam("paymentStatus", paymentStatus);
		}
		if(shippingStatus!=null){
			f.append(" and bean.itemOrder.shippingStatus=:shippingStatus");
			f.setParam("shippingStatus", shippingStatus);
		}
		f.append(" order by bean.id desc");
		return find(f, pageNo, pageSize);
	}
	
	@SuppressWarnings("unchecked")
	public List<OrderItem> getList(Long webId,Long productId,Long paymentId,Long shippingId,
			String code,Date startTime,Date endTime,Integer status){
		Finder f=Finder.create("select bean from OrderItem bean ");
		f.append(" where 1=1");
		if(webId!=null){
			f.append(" and bean.website.id=:webId");
			f.setParam("webId", webId);
		}
		if(productId!=null){
			f.append(" and bean.product.id=:productId");
			f.setParam("productId", productId);
		}
		if(paymentId!=null){
			f.append(" and bean.itemOrder.payment.id=:paymentId");
			f.setParam("paymentId", paymentId);
		}
		if(shippingId!=null){
			f.append(" and bean.itemOrder.shipping.id=:shippingId");
			f.setParam("shippingId", shippingId);
		}
		if(startTime!=null){
			f.append(" and bean.itemOrder.createTime>:startTime");
			f.setParam("startTime", startTime);
		}
		if(endTime!=null){
			f.append(" and bean.itemOrder.createTime<:endTime");
			f.setParam("endTime", endTime);
		}
		if(code!=null){
			f.append(" and bean.itemOrder.code like:code");
			f.setParam("code", "%"+code+"%");
		}
		if(status!=null){
			f.append(" and bean.itemOrder.status=:status");
			f.setParam("status", status);
		}
		f.append(" order by bean.id desc");
		return find(f);
	}
	
	
	public static String getHQL(Long ctgid,Long typeid){
		StringBuffer sb = new StringBuffer();
		sb.append("select bean.product.id, sum(bean.count), sum((bean.finalPrice-bean.costPrice)*bean.count) ");
		sb.append(" from OrderItem bean where bean.itemOrder.status=40 ");
		if(ctgid!=null){
			sb.append(" and bean.product.category.id=:ctgid ");
		}
		if(typeid!=null){
			sb.append(" and bean.product.type.id=:typeid ");
		}
		sb.append(" group by bean.product.id order by sum((bean.finalPrice-bean.costPrice)*bean.count) desc");
		return sb.toString();
	}
	
	public static String getCountHQL(Long ctgid,Long typeid){
		StringBuffer sb = new StringBuffer();
		sb.append("select count(DISTINCT bean.product.id) ");
		sb.append(" from OrderItem bean where bean.itemOrder.status=40 ");
		if(ctgid!=null){
			sb.append(" and bean.product.category.id=:ctgid ");
		}
		if(typeid!=null){
			sb.append(" and bean.product.type.id=:typeid ");
		}
		return sb.toString();
	}
	
	public List<Object[]> getOrderItem(){
		SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd");
		Calendar c=Calendar.getInstance();
		c.setTime(new Date());
		int i=c.get(Calendar.DAY_OF_WEEK);
		c.add(Calendar.DATE, -i);
		Date d=c.getTime();
		String endTime=sdf.format(d);
		c.add(Calendar.DATE, -7);
		Date dd=c.getTime();
		String beginTime=sdf.format(dd);
		
		String hql="select bean,sum(bean.count) from OrderItem bean" +
				" where bean.itemOrder.createTime<='"+endTime+"' and bean.itemOrder.createTime>'"+beginTime
				 +"' group by bean.product.id order by sum(bean.count) desc";
		List<Object[]> list=this.getSession().createQuery(hql).list();
		return list;
		
	}
	
	public OrderItem findByMember(Long memberId,Long productId){
		String hql="from OrderItem bean where bean.product.id=:productId and bean.itemOrder.member.id=:memberId";
	    Iterator<OrderItem> it=this.getSession().createQuery(hql).setParameter("memberId", memberId).setParameter("productId", productId)
	    .iterate();
	    if(it.hasNext()){
	    	return it.next();
	    }else{
	    	return null;
	    }
	}
	
	public List<OrderItem> getOrderItem(Long productId){
		String hql = "from OrderItem bean where bean.product.id=:productId and bean.itemOrder.status=40";
		List<OrderItem> list = this.getSession().createQuery(hql).setParameter("productId", productId).list();
		return list;
	}
	public List<OrderItem> getOrderItemList(Long productId,Integer pageNo,Integer pageSize){
		String hql = "from OrderItem bean where bean.product.id=:productId and bean.itemOrder.status=40";
		List<OrderItem> list = this.getSession().createQuery(hql).setParameter("productId", productId)
		.setFirstResult((pageNo-1)*pageSize).setMaxResults(pageSize).list();
		return list;
	}
	
}

